﻿CREATE PROCEDURE s_Collection_DDS_Avg AS
BEGIN TRAN

DECLARE @SQL varchar(max)

SET @SQL = CASE WHEN OBJECT_ID('Config_DDS_Avg') IS NULL THEN 'CREATE' ELSE 'ALTER' END
SELECT @SQL = @SQL+' FUNCTION Config_DDS_Avg()RETURNS int WITH SCHEMABINDING AS BEGIN RETURN '+
CONVERT(varchar,CONVERT(int,ROUND(AVG(DueDateSince),0)))+
' END'
FROM         usix.v_CustomerFile_Export_List
WHERE     ([Status Code] = 'O') AND ([Bankruptcy Filed Y_N?] = 'N') AND (LoanID > 1000000) AND (DPD BETWEEN 8 AND 60) AND DueDateSince > 0
AND     (CompanyID IN (1, 2, 5))

IF @@ERROR <> 0 BEGIN ROLLBACK RETURN END
PRINT (@SQL)
EXEC (@SQL)
COMMIT